#
#  Simple Data Store
#


from zoom import zoomer

from MySQLdb import IntegrityError

db = zoomer.db

class ValidException(Exception): pass
class TypeException(Exception): pass

def create_storage():
    zoomer.db("""
    create table if not exists storage_entities (
        id int not null auto_increment,
        kind      varchar(100),
        PRIMARY KEY (id)
        )
    """)
    zoomer.db("""
    create table if not exists storage_values (
        id int not null auto_increment,
        kind      varchar(100),
        row_id    int not null,
        attribute varchar(100),
        datatype  varchar(30),
        value     text,
        PRIMARY KEY (id)
        )
    """)

def delete_storage():
    zoomer.db('drop table if exists storage_values')
    zoomer.db('drop table if exists storage_entities')

class EntityList(list):
    def __str__(self):
        if len(self)==0:
            return 'Empty list'
        title=['%s\n    id  '%self[0].kind()]
        lines =['------- ']
        fmtstr = ['%6d  ']
        fields = ['id']

        data_lengths = {}
        for rec in self:
            for field in self[0].attributes():
                n = data_lengths.get(field,0)
                m = len('%s'%rec.__dict__.get(field,''))
                if n < m:
                    data_lengths[field] = m

        fields = data_lengths.keys()
        d = data_lengths
        fields.sort(lambda a,b:not d[a] and -999 or not d[b] and -999 or d[a]-d[b])
        fields = ['id'] + fields

        for field in fields[1:]:
            width = max(len(field),d[field])+1
            fmt = '%-' + ('%ds ' % width)
            fmtstr.append(fmt)
            title.append(fmt % field)
            lines.append(('-' * width) + ' ')
        fmtstr.append('\n')
        lines.append('\n')
        title.append('\n')
        t = []
        fmtstr = ''.join(fmtstr)
        for rec in self:
            values = [rec.__dict__.get(key) for key in fields]
            t.append(''.join(fmtstr) % tuple(values))
        return ''.join(title) + ''.join(lines) + ''.join(t)




class Model:

    def __init__(self,parent=None,**kv):
        for k in kv:
            if k == '_id':
                setattr(self,'id',kv[k])
            elif k <> 'id':
                setattr(self,k.lower(),kv[k])

    def update(self,**kv):
        for update_key in kv:
            found = 0
            for key in self.__dict__:
                if key.lower() == update_key.lower():
                    self.__dict__[key] = kv[update_key]
                    found = 1
            if not found:
                self.__dict__[update_key] = kv[update_key]

    @classmethod
    def kind(cls):
        """Returns the model/entity type
        """
        n = []
        for c in cls.__name__:
            if c.isalpha() or c=='_':
                if c.isupper() and len(n):
                    n.append('_')
                n.append(c.lower())
        return ''.join(n)

    def put(self):
        if not self.valid():
            return 0

        keys        = [i for i in self.__dict__.keys() if i<>'id']
        values      = [self.__dict__[i] for i in keys]
        datatypes   = [repr(type(i)).strip("<type >").strip("'") for i in values]
        valid_types = ['str','long','int','float','datetime.date','datetime.datetime','bool','NoneType']

        for atype in datatypes:
            if atype not in valid_types:
                raise TypeException,'unsupported type <type %s>' % atype

        if hasattr(self,'id') and self.exists(self.id):
            db('delete from storage_values where row_id=%s',self.id)

        else:
            db('insert into storage_entities (kind) values (%s)',self.kind())
            self.id = db.lastrowid

        keys = [key.lower() for key in keys] # store attribute names as lower case
        param_list = zip([self.kind()]*len(keys),[self.id]*len(keys),keys,datatypes,values)

        cmd = 'insert into storage_values (kind,row_id,attribute,datatype,value) values (%s,%s,%s,%s,%s)'
        db.cursor().executemany(cmd,param_list)

        return self.id

    def key(self):
        return self.id

    def delete(self):
        cmd = 'delete from storage_values where row_id=%s'
        db(cmd,self.id)

    @classmethod
    def exists(cls,keys=None):
        if not isinstance(keys, (list, tuple)):
            keys = (keys,)
        slots = (','.join(['%s']*len(keys)))
        cmd = 'select * from storage_entities where id in (%s)' % slots
        rs = db(cmd,*keys)

        found_keys = [rec.ID for rec in rs]
        if len(keys)>1:
            result = [(key in found_keys) for key in keys]
        else:
            result = keys[0] in found_keys
        return result

    @classmethod
    def get(cls,keys):
        if keys == None: return None
        if not isinstance(keys, (list, tuple)):
            keys = (keys,)
            as_list = 0
        else:
            as_list = 1
        keys = [long(key) for key in keys]

        if not keys:
            if as_list:
                return []
            else:
                return None

        cmd = 'select * from storage_values where kind=%s and row_id in (%s)' % ('%s',','.join(['%s']*len(keys)))
        entities = {}
        rs = db(cmd,cls.kind(),*keys)

        for rec in rs:
            row_id = rec.ROW_ID
            attribute = rec.ATTRIBUTE.lower()
            if rec.DATATYPE == "str":
                value = rec.VALUE
            elif rec.DATATYPE == "long":
                value = long(rec.VALUE)
            elif rec.DATATYPE == "int":
                value = int(rec.VALUE)
            elif rec.DATATYPE == 'float':
                value = float(rec.VALUE)
            elif rec.DATATYPE == "datetime.date":
                y = int(rec.VALUE[:4])
                m = int(rec.VALUE[5:7])
                d = int(rec.VALUE[8:10])
                import datetime
                value = datetime.date(y,m,d)
            elif rec.DATATYPE == "datetime.datetime":
                y = int(rec.VALUE[:4])
                m = int(rec.VALUE[5:7])
                d = int(rec.VALUE[8:10])
                hr = int(rec.VALUE[11:13])
                mn = int(rec.VALUE[14:16])
                sc = int(rec.VALUE[17:19])
                import datetime
                value = datetime.datetime(y,m,d,hr,mn,sc)
            elif rec.DATATYPE == 'bool':
                value = bool(rec.VALUE)
            elif rec.DATATYPE == 'NoneType':
                value = None
            elif rec.DATATYPE == 'instance':
#                print 'dzstore:','(id=%s name="%s" type=%s value=%s)'%(rec.id,attribute,repr(rec.DATATYPE),repr(rec.VALUE))
                value = long(rec.id)
            else:
                print 'dzstore:',rec.DATATYPE,'not supported (name="%s" type=%s value=%s id=%s)'%(attribute,repr(rec.DATATYPE),repr(rec.VALUE),rec.id)
                value = rec.VALUE
            entities.setdefault(row_id,cls(_id=row_id)).__dict__[attribute] = value

        if len(keys)>1:
            result = EntityList()
            for id in keys: result.append(entities.get(id))
#            result = [entities.get(id) for id in keys]
        else:
            if as_list:
                result = EntityList()
                result.append(entities.get(keys[0]))
            else:
                result = entities.get(keys[0])


        return result

    @classmethod
    def all(cls):
        cmd = 'select distinct row_id from storage_values where kind="%s" limit 1000' % cls.kind()
        rs = db(cmd)
        keys = [rec.ROW_ID for rec in rs]
        return cls.get(keys) or EntityList()

    @classmethod
    def zap(cls):
        cmd = 'delete from storage_values where kind=%s'
        db(cmd,cls.kind())
        cmd = 'delete from storage_entities where kind=%s'
        db(cmd,cls.kind())

    @classmethod
    def len(cls):
        cmd = 'select count(*) n from storage_entities where kind=%s'
        return db(cmd,cls.kind())[0].N

#     @classmethod
#     def find(cls,**kv):
#         all_keys = []
#         for field_name in kv.keys():
#             value = kv[field_name]
#             cmd = 'select distinct row_id from storage_values where kind=%s and attribute=%s and value=%s limit 1000'
#             rs = db(cmd,cls.kind(),field_name,value)
#             all_keys.append([rec.ROW_ID for rec in rs])
#         answer = set(all_keys[0])
#         for keys in all_keys[1:]:
#             answer = set(keys) & answer
#         return cls.get(list(answer))

    @classmethod
    def find(cls,**kv):
        all_keys = []
        for field_name in kv.keys():
            value = kv[field_name]
            if not isinstance(value, (list, tuple)):
                wc = 'value=%s'
                v = (value,)
            else:
                wc = 'value in ('+','.join(['%s']*len(value))+')'
                v = value
            cmd = 'select distinct row_id from storage_values where kind=%s and attribute=%s and '+wc+' limit 1000'
            rs = db(cmd,cls.kind(),field_name.lower(),*v)
            all_keys.append([rec.ROW_ID for rec in rs])
        answer = set(all_keys[0])
        for keys in all_keys[1:]:
            answer = set(keys) & answer
        if answer:
            return cls.get(list(answer))
        else:
            return []

    @classmethod
    def attributes(cls):
        cmd = 'select distinct attribute from storage_values where kind=%s'
        rs = db(cmd,cls.kind())
        values = [rec.ATTRIBUTE for rec in rs]
        return values

    def valid(self):
        return 1

    def __repr__(self):
        promt_len = 15
        return '\n'.join(['%s%s: %s' % (item[:promt_len], '.'*(promt_len-len(item)), self.__dict__[item]) for item in self.__dict__.keys()])

    def __getattr__(self,name):
        """Provide access to attributes, proxied attributes and calculated attributes"""
#        if name in self.__dict__:
#            return self.__dict__.get(name)
        if name.lower() in self.__dict__:
            return self.__dict__.get(name.lower())
#        elif hasattr(self.__class__,name):
#            return getattr(self.__class__,name)
        elif hasattr(self.__class__,name.lower()):
            return getattr(self.__class__,name.lower())
        elif hasattr(self.__class__,'get_'+name):
            method = getattr(self.__class__,'get_'+name)
            return method(self)
        else:
            raise AttributeError, name

    def __getitem__(self,key):
        return getattr(self,key)

if __name__ == '__main__':

    # don't use system database
    del db
    del zoomer.db

    # connect to test database
    import MySQLdb
    from dzdb import Database
    dbhost  = zoomer.config.get('database','dbhost')
    dbuser  = 'dzdev'
    dbname  = 'dz_test'
    dbpass  = 'password'
    zoomer.db = db = Database(MySQLdb.Connect,host=dbhost,user=dbuser,passwd=dbpass,db=dbname)
    print 'dzstore test\nconnected to %s on %s as %s' % (dbname,dbhost,dbuser)
    
    # Test Model
    class MyModel(Model):
        first_name = ''
        age = 0
        last_name  = ''

        def valid(self):
            return len(self.FIRST_NAME)>1

    import unittest

    class TestModelPerformance(unittest.TestCase):
        def stest_put_performance(self):
            num_puts = 100
            time_allowed = 6
            import time, random
            start = time.time()
            for i in range(num_puts):
                o = MyModel(FIRST_NAME='Joe%s'%i,LAST_NAME='Smith%s'%i,age=random.randint(0,50))
                o.put()
            assert time.time()-start<time_allowed,'%d puts exceeded %.2fs (%.2fs)' % (num_puts,time_allowed,time.time()-start)

        def stest_get_performance(self):
            num_gets = 5000
            time_allowed = 2
            import time, random
            start = time.time()
            for i in range(num_gets):
                r = MyModel().get(100)
            assert time.time()-start<time_allowed,'%d gets exceeded %.2fs (%.2fs)' % (num_gets,time_allowed,time.time()-start)

        def stest_all_performance(self):
            iterations = 5000
            time_allowed = 2
            import time, random
            start = time.time()
            for i in range(iterations):
                r = MyModel().all()
            assert time.time()-start<time_allowed,'%d operations exceeded %.2fs (%.2fs)' % (iterations,time_allowed,time.time()-start)


    class TestModel(unittest.TestCase):

        def test_init(self):
            assert MyModel()

        def test_create(self):
            o = MyModel(FIRST_NAME='Herb',LAST_NAME='Smith')
            self.assertEqual(o.FIRST_NAME,'Herb')
            o.put()
            o.FIRST_NAME = 'HERB'
            assert o.put()
            o = MyModel()
            o.FIRST_NAME = 'JOE'
            o.LAST_NAME  = 'SMITH'
            assert o.put()

        def test_put(self):
            o = MyModel()
            o.FIRST_NAME = 'HERB'
            assert o.put()
            o = MyModel()
            o.FIRST_NAME = 'JOE'
            o.LAST_NAME  = 'SMITH'
            assert o.put()

        def test_get(self):
            o = MyModel()
            MyModel.get(5)

        def test_get_missing(self):
            self.assertEqual(None,MyModel().get(100000))

        def test_get_multiple(self):
            o = MyModel()
            o.FIRST_NAME = 'Herb'
            o.AGE = 50
            rec_one = o.put()
            assert rec_one
            o = MyModel()
            o.FIRST_NAME = 'JOE'
            o.LAST_NAME  = 'SMITH'
            o.AGE = 30
            assert o.put()
            o = MyModel()
            o.FIRST_NAME = 'SARAH'
            o.AGE = 41
            rec_three = o.put()
            assert rec_three
            o = MyModel()
            o.FIRST_NAME = 'WENDEL'
            o.LAST_NAME  = 'SMITH'
            o.AGE = 40
            assert o.put()
            result = MyModel.get([rec_one,rec_three])
            self.assertEqual(result[0].FIRST_NAME,'Herb')
            self.assertEqual(result[0].AGE,50)
            self.assertEqual(result[1].AGE,41)

        def test_get_all(self):
            o = MyModel()
            o.FIRST_NAME = 'HERB'
            assert o.put()
            o = MyModel()
            o.FIRST_NAME = 'JOE'
            o.LAST_NAME  = 'SMITH'
            assert o.put()
            o = MyModel()
            o.FIRST_NAME = 'SARAH'
            assert o.put()
            o = MyModel()
            o.FIRST_NAME = 'WENDEL'
            o.LAST_NAME  = 'SMITH'
            assert o.put()
            MyModel.all()

        def test_put_new_then_get(self):
            o = MyModel()
            o.FIRST_NAME = 'Herb'
            new_id = o.put()
            assert new_id
            self.assertEqual(o.FIRST_NAME,'Herb')
            del o
            r = MyModel().get(new_id)
            self.assertEqual(r.FIRST_NAME,'Herb')

        def test_dont_put_invalid_object(self):
            o = MyModel()
            o.LAST_NAME = 'Herb'
            assert not o.put()

        def test_kind(self):
            o = MyModel()
            self.assertEqual(o.kind(),'my_model')

        def test_find(self):
            MyModel(FIRST_NAME='Herb',REGION='A',AGE=15).put()
            MyModel(FIRST_NAME='Joe',REGION='A',AGE=10).put()
            MyModel(FIRST_NAME='James',REGION='A',AGE=20).put()
            MyModel(FIRST_NAME='Shirley',REGION='A',AGE=10).put()
            MyModel(FIRST_NAME='Shea',REGION='B',AGE=10).put()
            
            self.assertEqual(MyModel.find(FIRST_NAME='HERB')[0].FIRST_NAME,'Herb')
            result = MyModel.find(FIRST_NAME='Joe')
            self.assertEqual(result[0].FIRST_NAME,'Joe')
            self.assertEqual(MyModel.find(FIRST_NAME='SAM'),[])

            self.assertEqual(len(MyModel.find(REGION='A',AGE=10)),2)
            self.assertEqual(len(MyModel.find(REGION='A')),4)
            self.assertEqual(len(MyModel.find(AGE=10)),3)

            self.assertEqual(len(MyModel.find(FIRST_NAME=['Herb','Joe'])),2)

#         def test_find_all(self):
#             MyModel(FIRST_NAME='Herb',REGION='A',AGE=15).put()
#             MyModel(FIRST_NAME='Joe',REGION='A',AGE=10).put()
#             MyModel(FIRST_NAME='James',REGION='A',AGE=20).put()
#             MyModel(FIRST_NAME='Shirley',REGION='A',AGE=10).put()
#             MyModel(FIRST_NAME='Shea',REGION='B',AGE=10).put()
#
#             self.assertEqual(MyModel.find_all(FIRST_NAME='HERB')[0].FIRST_NAME,'Herb')
#             result = MyModel.find(FIRST_NAME='Joe')[0]
#             self.assertEqual(result.FIRST_NAME,'Joe')
#             self.assertEqual(MyModel.find_all(FIRST_NAME='SAM'),[])

        def test_attributes(self):
            MyModel(FIRST_NAME='Joe',LAST_NAME='Smith',AGE=21).put()
            attributes = MyModel().attributes()
            assert 'first_name' in attributes
            assert 'last_name' in attributes
            assert 'age' in attributes
            assert len(attributes)==3

        def test_float(self):
            class TheModel(Model): pass
            import time
            TheModel(FIRST_NAME='Joe',LAST_NAME='Smith',EXPIRY=time.time()+10*60).put()
            attributes = TheModel().attributes()
#            print attributes
            assert 'first_name' in attributes
            assert 'last_name' in attributes
            assert 'expiry' in attributes
            assert len(attributes)==3
#            print TheModel.all()

        def test_instance(self):
            class TestCustomer(Model): pass
            class TestInvoice(Model): pass
            customer = TestCustomer(name='Joe Smith',credit_limit=100)
            assert customer
            customer_id = customer.put()
            assert customer_id
            invoice = TestInvoice(customer=customer,amount=25,items='here are the items')
            self.assertRaises(TypeException,invoice.put)
            invoice = TestInvoice(customer=customer_id,amount=25,items='here are the items')
            invoice_id = invoice.put()
            assert invoice_id
            invoice = TestInvoice.get(invoice_id)
            self.assertEqual(invoice.items,'here are the items')
            self.assertEqual(invoice.amount,25)
            customer = TestCustomer.get(invoice.customer)
            assert customer
            self.assertEqual(customer.name,'Joe Smith')


        def test_with_methods(self):
            class TheModel(Model): 
                def mymethod(self):
                    return self.last_name.upper() + ',' + self.first_name.upper()
            import time
            TheModel(FIRST_NAME='Joe',LAST_NAME='Smith',EXPIRY=time.time()+10*60).put()
            attributes = TheModel().attributes()
            assert 'first_name' in attributes
            assert 'last_name' in attributes
            assert 'expiry' in attributes
            assert len(attributes)==3
            #print TheModel.all()

        def test_zap_and_len(self):
            MyModel.zap()
            self.assertEqual(MyModel.len(),0)
            rec = MyModel()
            rec.FIRST_NAME = 'Fred'
            rec.put()
            self.assertEqual(MyModel.len(),1)
            MyModel.zap()
            self.assertEqual(MyModel.len(),0)

        def test_exists(self):
            MyModel.zap()
            self.assertEqual(MyModel.len(),0)
            rec = MyModel()
            rec.FIRST_NAME = 'Fred'
            new_id = rec.put()
            self.assertEqual(MyModel.exists(new_id),True)
            self.assertEqual(MyModel.len(),1)
            MyModel.zap()
            self.assertEqual(MyModel.len(),0)
            self.assertEqual(MyModel.exists(new_id),False)

        def test_update(self):
            MyModel.zap()
            rec = MyModel()
            rec.FIRST_NAME = 'Fred'
            rec.put()
            rec.FIRST_NAME = 'Herb'
            id = rec.put()
            self.assertEqual(MyModel.len(),1)
            o = MyModel.get(id)
            self.assertEqual(MyModel.get(id).FIRST_NAME,'Herb')

            rec.update(FIRST_NAME='Joey')
            rec.put()
            #print MyModel.get(id)

        def test_sort(self):
            MyModel.zap()
            MyModel(FIRST_NAME='Joe',LAST_NAME='Smith',AGE=20).put()
            MyModel(FIRST_NAME='Adam',LAST_NAME='Jones',AGE=40).put()
            t = MyModel.all()
            t.sort(key=lambda obj: obj.FIRST_NAME)
            self.assertEqual(t[0].FIRST_NAME,'Adam')
            t.sort(key=lambda obj: obj.AGE)
            self.assertEqual(t[0].FIRST_NAME,'Joe')

        def test_case_agnostic(self):
            MyModel.zap()
            new_id = MyModel(FIRST_NAME='Joe',LAST_NAME='Smith').put()

            m = MyModel.get(new_id)
            self.assertEqual(hasattr(m,'first_name'),True)
            self.assertEqual(hasattr(m,'FIRST_NAME'),True)

            MyModel.zap()
            new_id = MyModel(FIRST_NAME='Joe',last_name='Smith').put()
            m = MyModel.get(new_id)
            self.assertEqual(hasattr(m,'last_name'),True)
            self.assertEqual(hasattr(m,'LAST_NAME'),True)

            m.update(Last_Name='Smithers',age=20)
            another_new_id = m.put()

            n = MyModel.get(another_new_id)
            assert hasattr(n,'last_name')
            self.assertEqual(n.last_name,'Smithers')

        def test_none(self):
            MyModel.zap()
            new_id = MyModel(FIRST_NAME='Joe',LAST_NAME='Smith',STATUS=None).put()

            m = MyModel.get(new_id)
            self.assertEqual(hasattr(m,'first_name'),True)
            assert hasattr(m,'first_name')
            assert m.first_name == 'Joe'
            assert m.status == None

#            print n.id

#            print MyModel.all()

#            print zoomer.db('select * from storage_values')


    unittest.main()


